

/************************************************************************
   This CREATE statement is used only for compiling the procedure, that
   is why it is contained Before the create procedure statement.  The
   temporary table at execution time is created in p280_p006.
 ************************************************************************/

create table #t280_fypd	
( f_invtyp_c   char(14) null
, f_invtypnm_x char(30) null
, f_inv1_q     money null
, f_inv1_a     money null
, f_inv2_q     money null 
, f_inv2_a     money null
)      
go
drop proc p280_p006a
go

create procedure p280_p006a
/*
  -----------------------------------------------------------------------
      P280_P006A - Inventory Analysis Report - SUMMARY TABLE t280sum.
                  (For Reports without GRP,DIV,BUS)
  -----------------------------------------------------------------------
*/

/*
    ------  INPUT VARIABLES   ------
*/
   @bpd char(6)   = " " 
 , @epd char(6)   = " "
 , @loc char(3)   = " "
 , @div char(3)   = " " 
 , @dpt char(4)   = " "
 , @inv char(4)   = " "
 , @src char(6)   = " "
 , @stg char(3)   = " "
 , @fop char(6)   = " "
 , @afm char(3)   = " "
 , @srt char(15)  = " "
 , @glv char(1)   = "V"
 
with recompile
as
/*
    ------ INTERNAL VARIABLES ------
*/

/***********************************************************************
   Control Section for the Summary Reports
 ***********************************************************************/

if @srt = 'TYPE' 
   goto RptType

if @srt = 'LOC'  
   goto RptLoc 

if @srt = 'DIV'  
   goto RptDiv 

if @srt = 'DEPT' 
   goto RptDpt 

if @srt = 'FAMILY'
   goto RptFamily

if @srt = 'STAGE'
   goto RptStage

if @srt = 'FOP'  
   goto RptFOP 

if @srt = 'SOURCE'
   goto RptSource

RptType:
begin
insert #t280_fypd 
select a.f_invtyp_c+a.f_invtyp_x	
     , a.f_invtypnm_x
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280sum  b                                                  
     , d280db1.dbo.t280type a                                                   
 where a.f_invtyp_c  = b.f_invtyp_c                                       
   and a.f_invtyp_c  != '0000'                                             
   and b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_glval_i   like @glv
 group by a.f_invtyp_c
        , a.f_invtyp_x
        , a.f_invtypnm_x

union all         
select a.f_invtyp_c+a.f_invtyp_x	
     , a.f_invtypnm_x
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280sum  b                                                  
     , d280db1.dbo.t280type a                                                   
 where a.f_invtyp_c  = b.f_invtyp_c                                       
   and a.f_invtyp_c  != '0000'                                             
   and b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_glval_i   like @glv
 group by a.f_invtyp_c
        , a.f_invtyp_x
        , a.f_invtypnm_x
union all
select a.f_invtyp_c+a.f_invtyp_x	
     , a.f_invtypnm_x
     , 0
     , $0.00
     , 0
     , $0.00
  from d280db1.dbo.t280type a                                                  
 where a.f_invtyp_c  != '0000'                                             
            
end
   goto ENDIT   

RptLoc: 
begin
insert #t280_fypd 
select '    ' 
     , b.f_loc_c
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280sum  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_glval_i   like @glv
 group by b.f_loc_c
union all   
select '    ' 
     , b.f_loc_c
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280sum  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_glval_i   like @glv
 group by b.f_loc_c
            
end
   goto ENDIT   


RptDiv: 
begin
insert #t280_fypd 
select '    ' 
     , b.f_div_c
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280sum  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_glval_i   like @glv
 group by b.f_div_c
union all   
select '    '
     , b.f_div_c
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280sum  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_glval_i   like @glv
 group by b.f_div_c
            
end
   goto ENDIT   


RptDpt: 
begin
insert #t280_fypd 
select '    '
     , b.f_dpt_c
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280sum  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_glval_i   like @glv
 group by b.f_dpt_c
union all   
select '    '
     , b.f_dpt_c 
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280sum  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_glval_i   like @glv
 group by b.f_dpt_c   
            
end
   goto ENDIT   


RptFamily:
begin
insert #t280_fypd 
select '    '
     , b.f_afm_c	
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280sum  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_glval_i   like @glv
 group by b.f_afm_c   
union all   
select '    '      	
     , b.f_afm_c 
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280sum  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_glval_i   like @glv
 group by b.f_afm_c
            
end
   goto ENDIT   


RptStage: 
begin
insert #t280_fypd 
select '    '      	
     , b.f_stg_c     	
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280sum  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_glval_i   like @glv
 group by b.f_stg_c
union all   
select '    '    
     , b.f_stg_c
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280sum  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_glval_i   like @glv
 group by b.f_stg_c
            
end
   goto ENDIT   


RptFOP:  
begin
insert #t280_fypd 
select '    '      	
     , b.f_fop_c  
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280sum  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_glval_i   like @glv
 group by b.f_fop_c   
union all   
select '    '   	
     , b.f_fop_c
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280sum  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_glval_i   like @glv
 group by b.f_fop_c
            
end
   goto ENDIT   


RptSource:
begin
insert #t280_fypd 
select '    '  
     , b.f_syssrc_c  	
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280sum  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_glval_i   like @glv
 group by b.f_syssrc_c
union all   
select '    '
     , b.f_syssrc_c  	
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280sum  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_glval_i   like @glv
 group by b.f_syssrc_c
            
end
   goto ENDIT   


ENDIT: 

/************************************************************************
   Return to the calling environment
 ************************************************************************/

return ( 0 )
go
